import aiosqlite
from contextlib import asynccontextmanager
from typing import AsyncGenerator, Any, Dict, List, Optional, Tuple
from .config import pkgm_config


class DatabaseManager:
    def __init__(self):
        self.url = pkgm_config.sqlite_db_path
        self._connection: Optional[aiosqlite.Connection] = None

    async def initialize(self) -> None:
        if not self._connection:
            self._connection = await aiosqlite.connect(self.url)
            # Enable foreign key support and other SQLite settings as needed
            await self._connection.execute("PRAGMA foreign_keys = ON")

    async def close(self) -> None:
        if self._connection:
            await self._connection.close()
            self._connection = None

    @asynccontextmanager
    async def get_connection(self) -> AsyncGenerator[aiosqlite.Connection, None]:
        try:
            yield self._connection
        except Exception as e:
            raise RuntimeError(f"Database operation failed: {e}")

    async def execute(self, query: str, parameters: Optional[Tuple[Any, ...]] = None) -> None:
        async with self.get_connection() as conn:
            cursor = await conn.execute(query, parameters or ())
            await conn.commit()
    
    async def execute_file(self, sql_text):
        async with self.get_connection() as conn:
            cursor = await conn.executescript(sql_text)
            await conn.commit()

    async def fetch_all(self, query: str, parameters: Optional[Tuple[Any, ...]] = None) -> List[Dict[str, Any]]:
        async with self.get_connection() as conn:
            cursor = await conn.execute(query, parameters or ())
            columns = [column[0] for column in cursor.description]
            rows = await cursor.fetchall()
            return [dict(zip(columns, row)) for row in rows]

    async def fetch_one(self, query: str, parameters: Optional[Tuple[Any, ...]] = None) -> Optional[Dict[str, Any]]:
        async with self.get_connection() as conn:
            cursor = await conn.execute(query, parameters or ())
            row = await cursor.fetchone()
            if row:
                columns = [column[0] for column in cursor.description]
                return dict(zip(columns, row))
            return None

    async def create_table(self, table_name: str, columns: Dict[str, str]) -> None:
        columns_with_types = ", ".join([f"{col} {ctype}" for col, ctype in columns.items()])
        query = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_with_types})"
        await self.execute(query)

    async def insert(self, table_name: str, data: Dict[str, Any]) -> int:
        keys = ", ".join(data.keys())
        placeholders = ", ".join(["?" for _ in data])
        query = f"INSERT INTO {table_name} ({keys}) VALUES ({placeholders})"
        async with self.get_connection() as conn:
            cursor = await conn.execute(query, tuple(data.values()))
            await conn.commit()
            return cursor.lastrowid

    async def update(self, table_name: str, data: Dict[str, Any], condition: str, condition_values: Tuple[Any, ...]) -> None:
        set_clause = ", ".join([f"{key} = ?" for key in data.keys()])
        query = f"UPDATE {table_name} SET {set_clause} WHERE {condition}"
        values = tuple(data.values()) + condition_values
        await self.execute(query, values)

    async def delete(self, table_name: str, condition: str, condition_values: Tuple[Any, ...]) -> None:
        query = f"DELETE FROM {table_name} WHERE {condition}"
        await self.execute(query, condition_values)


db_manager = DatabaseManager()


async def get_db() -> AsyncGenerator[DatabaseManager, None]:
    yield db_manager
